# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
（樊登）呼出历史
"""
import pymysql
from util import (
    time,
    logging,
    write_excel,
    json,
    callout_connect_status_infos,
    per_excel_data_num,
    db_ip,
    db_port,
    db_username,
    db_password,
    excel_save_dir,
)


class ExportCallOutHistoryFD:
    def __init__(self, ui_data):
        self.data_date = ui_data.get("data_date")
        self.task_ids = ui_data.get("target_datas")
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        self.start_time = ui_data.get("start_time")
        self.end_time = ui_data.get("end_time")
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")

    def query_count(self, task_id):
        """先查询数据量"""
        sql_count = f"""select count(*) from {self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} 
        where task_id={task_id} and callout_finish_time BETWEEN '{self.start_time}' AND '{self.end_time}';"""
        self.cursor.execute(sql_count)
        count = self.cursor.fetchone()[0]
        logging.info(
            f"查询到{self.business}任务ID为{task_id}的{self.data_type}共计{count}条。"
        )
        return count

    def export(self):
        """樊登呼出历史导出"""
        task_id_num = len(self.task_ids)
        if task_id_num == 0:  # 不输入任务ID，导出所有存在拨打数据的任务的呼出历史数据
            # 先查询出时间范围内有哪些task_id有通话
            need_handle_task_ids = list()
            logging.info(
                f"开始查询{self.business}从{self.start_time}到{self.end_time}全部任务的{self.data_type}..."
            )
            query_task_id_sql = f"""SELECT task_id 
                        FROM {self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} where 
                        callout_finish_time BETWEEN '{self.start_time}' and '{self.end_time}' group by task_id;"""
            query_start_time = time.time()
            self.cursor.execute(query_task_id_sql)
            query_task_id_result = self.cursor.fetchall()
            query_end_time = time.time()
            query_consumer_time = query_end_time - query_start_time
            logging.info(
                f"查询{self.business}在{self.start_time}至{self.end_time}之间进行外呼的任务ID"
                f"共计耗时{query_consumer_time:.2f}秒。"
            )
            query_task_id_num = len(query_task_id_result)  # 有呼叫历史的task_id个数
            if query_task_id_num == 0:  # 查询不到有拨打记录的任务ID，说明没有拨打数据
                msg = f"在指定的时间范围内，{self.business}没有{self.data_type}数据。"
                logging.warning(msg)
                return {"code": 1, "excel_names": [], "msg": msg}
            else:
                for x in query_task_id_result:
                    task_id = x[0]
                    need_handle_task_ids.append(task_id)
                logging.info(
                    f"查询{self.business}从{self.start_time}到{self.end_time}有通话记录的任务ID"
                    f"有：\n{need_handle_task_ids}"
                )
        else:
            need_handle_task_ids = self.task_ids
        excel_names = list()
        for task_id in need_handle_task_ids:
            total_count = self.query_count(task_id)
            total_batches = (total_count + per_excel_data_num - 1) // per_excel_data_num
            for i in range(total_batches):
                sql = f"""select a.task_id,b.task_name,a.line_number,a.detail_info,a.dial_number,
                a.dial_num_district,a.callout_connect_status,DATE_FORMAT(a.callout_dial_time, '%Y-%m-%d %H:%i:%s'),
                a.dialog_duration,a.callout_last_node_name,a.system_error,a.system_error_desc,a.dialog_user_prefer,
                a.memo from {self.db_name}.b_{self.table_index}_callout_session_ach_{self.data_date} a 
                left join {self.db_name}.b_{self.table_index}_callout_task b on a.task_id=b.task_id  
                where a.task_id={task_id} LIMIT {i * per_excel_data_num}, {per_excel_data_num};"""
                logging.info(
                    f"开始查询{self.business}任务ID为{task_id}的第[{i + 1}/{total_batches}]"
                    f"批次{self.data_type}:\n{sql}"
                )
                query_start_time = time.time()
                self.cursor.execute(sql)
                result = self.cursor.fetchall()
                query_end_time = time.time()
                query_take_time = query_end_time - query_start_time
                logging.info(
                    f"查询{self.business}任务ID为{task_id}的第[{i + 1}/{total_batches}]批次{self.data_type}"
                    f"耗时{query_take_time:.2f}秒。"
                )
                excel_data = []
                excel_title = [
                    "任务ID",
                    "任务名",
                    "主叫号码",
                    "caseId",
                    "被叫号码",
                    "归属地",
                    "呼出状态",
                    "呼出时间",
                    "通话时长(秒)",
                    "挂断节点",
                    "异常码",
                    "异常描述",
                    "客户意向",
                    "备注",
                ]
                if total_batches > 1:
                    file_name = f"{self.business}_{self.data_type}_任务ID({task_id})_第{i + 1}批"
                else:
                    file_name = f"{self.business}_{self.data_type}_任务ID({task_id})"
                for x in result:
                    (
                        task_id,
                        task_name,
                        line_number,
                        detail_info,
                        dial_number,
                        dial_num_district,
                        callout_connect_status,
                        callout_dial_time,
                        dialog_duration,
                        callout_last_node_name,
                        system_error,
                        system_error_desc,
                        dialog_user_prefer,
                        memo,
                    ) = x
                    callout_connect_status_desc = callout_connect_status_infos.get(
                        callout_connect_status
                    )
                    case_id = json.loads(detail_info).get("caseId")
                    dialog_duration_s = dialog_duration / 1000
                    excel_data.append(
                        [
                            task_id,
                            task_name,
                            line_number,
                            case_id,
                            dial_number,
                            dial_num_district,
                            callout_connect_status_desc,
                            callout_dial_time,
                            dialog_duration_s,
                            callout_last_node_name,
                            system_error,
                            system_error_desc,
                            dialog_user_prefer,
                            memo,
                        ]
                    )
                sheet_info = [[self.data_type, excel_title, excel_data]]
                excel_name = write_excel(sheet_info, excel_save_dir, file_name, True)
                excel_names.append(excel_name)
        return {"code": 0, "excel_names": excel_names, "msg": None}


if __name__ == "__main__":
    ...
